package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAccountCombination;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MElementValue;
import org.compiere.model.MOrg;
import org.compiere.model.MProduct;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

public class MM_Form_296a extends SvrProcess {
	//MM_Form_296a
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_Account_ID, p_M_Product_ID, p_P_Asset_Acct;
	
	Date DateAcct;

	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("P_Asset_Acct")) 
				p_P_Asset_Acct  = para[i].getParameterAsInt();
			else if (name.equals("M_Product_ID")) 
				p_M_Product_ID = para[i].getParameterAsInt();
			else if (name.equals("DateAcct")) 
				DateAcct = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_296a_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 11) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		p_Account_ID = new MAccountCombination(m_ctx, p_P_Asset_Acct, get_TrxName()).getAccount_ID();
		
		//Date Cell Styling
		CellStyle ds = wb.createCellStyle();
		ds.setBorderBottom((short)1);
		ds.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		ds.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		ds.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		//DocType
		CellStyle dts = wb.createCellStyle();
		dts.setBorderBottom((short)1);
		dts.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		dts.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		dts.setVerticalAlignment(CellStyle.ALIGN_LEFT);
		//New Cell Style
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderTop((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		cs.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Header
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(13).getCell(3).setCellValue((new MElementValue(m_ctx, p_Account_ID, get_TrxName()).getValue()) + "-" +(new MElementValue(m_ctx, p_Account_ID, get_TrxName()).getName()));
		sheet.getRow(13).getCell(39).setCellValue((new MProduct(m_ctx, p_M_Product_ID, get_TrxName()).getValue()) + '-' + (new MProduct(m_ctx, p_M_Product_ID, get_TrxName()).getName()));
		
		//Positions
		int row = 19;
		PreparedStatement pstmtrb = null;
		ResultSet rsrb = null;
		String sqlrb =  "select (date_trunc('year', timestamp '" + DateAcct + "')) AS DateAcct" + 
					    ", (NULL)::numeric Record_ID" + 
					    ", (NULL)::character varying DocumentNo" + 
					    ", ('Остатки на начало года')::character varying DocType" + 
					    ", (NULL)::character varying BPartner" + 
					    ", UOM" + 
					    ", (NULL)::numeric ColPrice" + 
					    ", (NULL)::numeric AS DebitQty" + 
					    ", (NULL)::numeric AS DebitAmount" + 
					    ", (NULL)::numeric AS CreditQty" + 
					    ", (NULL)::numeric AS CreditAmount" + 
					    ", sum(RemainQty) AS RemainQty" + 
					    ", sum(RemainAmount) AS RemainAmount " +  
					"from MM_Turnover_V " + 
					"where DateAcct between '01/01/1990' AND (date_trunc('year', timestamp '" + DateAcct + "') - interval '1 day') " +
					    "and (Account_ID = " + p_Account_ID + " or " + p_Account_ID + " is null) " + 
					    "and M_Product_ID = " + p_M_Product_ID + 
					" group by UOM";
		try {
			pstmtrb = DB.prepareStatement(sqlrb.toString(), get_TrxName());
			rsrb = pstmtrb.executeQuery();
			
			while (rsrb.next()) {
				
				//Shifting and Merging
				sheet.shiftRows(row, row + 11, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 4));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 5, 9));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 10, 20));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 21, 30));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 33));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 36));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 37, 40));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 41, 45));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 46, 49));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 50, 54));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 55, 58));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 59, 63));
				
				R = sheet.createRow(row);
				for(int j = 0; j < 64; j++){
					C = R.createCell(j);
					C.setCellStyle(cs);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(rsrb.getTimestamp("DateAcct"));
				sheet.getRow(row).getCell(0).setCellStyle(ds);
				sheet.getRow(row).getCell(5).setCellValue(rsrb.getString("DocumentNo"));
				sheet.getRow(row).getCell(10).setCellValue(rsrb.getString("DocType"));
				sheet.getRow(row).getCell(10).setCellStyle(dts);
				sheet.getRow(row).getCell(21).setCellValue(rsrb.getString("BPartner"));
				sheet.getRow(row).getCell(21).setCellStyle(dts);
				sheet.getRow(row).getCell(31).setCellValue(rsrb.getString("UOM"));
				sheet.getRow(row).getCell(34).setCellValue(rsrb.getInt("ColPrice"));
				sheet.getRow(row).getCell(37).setCellValue(rsrb.getInt("DebitQty"));
				sheet.getRow(row).getCell(41).setCellValue(rsrb.getInt("DebitAmount"));
				sheet.getRow(row).getCell(46).setCellValue(rsrb.getInt("CreditQty"));
				sheet.getRow(row).getCell(50).setCellValue(rsrb.getInt("CreditAmount"));
				sheet.getRow(row).getCell(55).setCellValue(rsrb.getInt("RemainQty"));
				sheet.getRow(row).getCell(59).setCellValue(rsrb.getInt("RemainAmount"));

				row++;
			}
			
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sqlrb.toString(), e);
		}
		finally {
			DB.close(rsrb, pstmtrb);
			rsrb = null;
			pstmtrb = null;
		}
		
		Calendar cal = new GregorianCalendar();
		cal.setTime(DateAcct);
		int ai = cal.get(Calendar.MONTH) + 1;
		PreparedStatement pstmtturnover = null;
		ResultSet rsturnover = null;
	
		for(int i = 0; i < ai; i++){
			cal.set(Calendar.MONTH, i);
			DateAcct = cal.getTime();
			String sqlturnover =  "select * FROM MM_QtyAmtAcct(" + 
									p_Account_ID + ", " + 
									p_M_Product_ID + ", '" + 
									DateAcct + "')";
			
			try {
				pstmtturnover = DB.prepareStatement(sqlturnover.toString(), get_TrxName());
				rsturnover = pstmtturnover.executeQuery();
				
				while (rsturnover.next()) {
					
					//Shifting and Merging
					sheet.shiftRows(row, row + 11, 1);
					sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 4));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 5, 9));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 10, 20));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 21, 30));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 33));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 36));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 37, 40));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 41, 45));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 46, 49));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 50, 54));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 55, 58));
					sheet.addMergedRegion(new CellRangeAddress(row, row, 59, 63));
					
					R = sheet.createRow(row);
					for(int j = 0; j < 64; j++){
						C = R.createCell(j);
						C.setCellStyle(cs);
					}
					
					sheet.getRow(row).getCell(0).setCellValue(rsturnover.getTimestamp("DateAcct"));
					sheet.getRow(row).getCell(0).setCellStyle(ds);
					sheet.getRow(row).getCell(5).setCellValue(rsturnover.getString("DocumentNo"));
					sheet.getRow(row).getCell(10).setCellValue(rsturnover.getString("DocType"));
					sheet.getRow(row).getCell(10).setCellStyle(dts);
					sheet.getRow(row).getCell(21).setCellValue(rsturnover.getString("BPartner"));
					sheet.getRow(row).getCell(21).setCellStyle(dts);
					sheet.getRow(row).getCell(31).setCellValue(rsturnover.getString("UOM"));
					sheet.getRow(row).getCell(34).setCellValue(rsturnover.getInt("ColPrice"));
					sheet.getRow(row).getCell(37).setCellValue(rsturnover.getInt("DebitQty"));
					sheet.getRow(row).getCell(41).setCellValue(rsturnover.getInt("DebitAmount"));
					sheet.getRow(row).getCell(46).setCellValue(rsturnover.getInt("CreditQty"));
					sheet.getRow(row).getCell(50).setCellValue(rsturnover.getInt("CreditAmount"));
					sheet.getRow(row).getCell(55).setCellValue(rsturnover.getInt("RemainQty"));
					sheet.getRow(row).getCell(59).setCellValue(rsturnover.getInt("RemainAmount"));
	
					
					row++;
				}
				
			}
			catch (Exception e) {
				log.log(Level.SEVERE, sqlturnover.toString(), e);
			}
			finally {
				DB.close(rsturnover, pstmtturnover);
				rsturnover = null;
				pstmtturnover = null;
			}
		}

		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();

		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
